#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2022/11/24 11:48
# @Author : 闫峰
# @File    : sql_server_utils.py
# encoding=utf8


import pyodbc
from util.readFileUtils.yamlControl import GetYamlData


class MSSQL:

    def __init__(self, yaml_path, database_name):
        """
        yaml_path 数据库配置信息yaml 文件地址
        database_name  yaml文件中的数据库信息 key
        """
        mysql = GetYamlData(yaml_path).get_yaml_data()[database_name]
        # print(mysql)
        self.server = mysql['host']
        self.username = mysql['user']
        self.password = mysql['password']
        self.database = mysql['db_name']
        self.conn = None
        self.cursor = None

    def __GetConnect(self):
        """
        得到连接信息
        返回: conn.cursor()
        """
        import os

        def get_os():
            if os.name == 'nt':
                return 'Windows'
            elif os.name == 'posix':
                return 'Linux'  # 或者 'Unix' 或 'Mac'，取决于具体实现
            else:
                return 'Unknown OS'

        # 打印当前操作系统
        print("Running on:", get_os())
        if not self.database:
            raise (NameError, "没有设置数据库信息")
        if get_os() == 'Windows':

            self.conn = pyodbc.connect(
                'DRIVER={SQL Server};SERVER=' + self.server + ';DATABASE=' + self.database + ';UID=' + self.username + ';PWD=' + self.password)
        elif get_os() == 'Linux':

            self.conn = pyodbc.connect(
                'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + self.server + ';DATABASE=' + self.database + ';UID=' + self.username + ';PWD=' + self.password)
        self.cursor = self.conn.cursor()
        if not self.cursor:
            raise (NameError, "连接数据库失败")
        else:
            return self.cursor

    def ExecQuery(self, sql, value):
        """
        执行查询语句
        返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段

        """
        cur = self.__GetConnect()
        cur.execute(sql, value)
        resList = cur.fetchall()

        # 查询完毕后必须关闭连接
        cur.close()
        self.conn.close()
        return resList

    def ExecNonQuery(self, sql):
        """
        执行非查询语句

        调用示例：
            cur = self.__GetConnect()
            cur.execute(sql)
            self.conn.commit()
            self.conn.close()
        """
        cur = self.__GetConnect()
        cur.execute(sql)
        self.conn.commit()
        self.conn.close()

        # return result

    def close(self):
        self.close()
